package dbtools;

/**
 * Title: 数据表信息高速缓存
 * Desc:
 */


import dbtools.DataSimpDto;

import java.util.ArrayList;
import java.util.HashMap;


public class DBTableInfo {
	private static DBTableInfo instance = null;
	private static HashMap fieldInfo = new HashMap();
	private static HashMap pkInfo = new HashMap();

	private static HashMap fieldInfos = new HashMap();
	private static ArrayList fieldnamelist = new ArrayList();

	private static HashMap fieldInfosAll = new HashMap();
	private static ArrayList allfieldnamelist = new ArrayList();

	// ----

	// 存放 数据表信息

	// key -- 表名 （小写）
	// value -- String[] 0--字段名称 1-类型 2--是否主键 3-最大长度 4-是否可空 5-最大小数位 6-注释

	private DBTableInfo() {
		fieldInfo = new HashMap();
		pkInfo = new HashMap();
		fieldnamelist = new ArrayList();
		fieldInfos = new HashMap();

		allfieldnamelist = new ArrayList();
		fieldInfosAll = new HashMap();
	}

	public static void MakeTableInfo(String tablename) {
		HashMap tmphashmap = new HashMap();

		fieldnamelist = new ArrayList();
		allfieldnamelist = new ArrayList();




		String currentdb = "mysql";

		int pub_jlzt = 0;
		int pub_fjbj = 0;
		int nomsign = 0;


		if (currentdb.toLowerCase().startsWith("mysql")) {

			String sqlStr = " select a.column_NAME,";
			sqlStr += " a.data_TYPE,a.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_SCALE,a.NUMERIC_PRECISION,a.IS_NULLABLE,a.column_key,a.column_COMMENT from columns a";
			sqlStr += " where " + " trim(a.table_name)='"
					+ tablename.toUpperCase().trim() + "'";

			DataSimpDto tmpdto = new DataSimpDto("mysqltx", sqlStr);

			nomsign = 0;
			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("column_NAME", s); // 字段名称
				String fieldtype = tmpdto.getDataValue("data_TYPE", s); // 类型
				String ifpk = tmpdto.getDataValue("column_key", s); // 是否主键
				String maxlength = tmpdto.getDataValue(
						"CHARACTER_MAXIMUM_LENGTH", s); // 最大长度

				String isnull = tmpdto.getDataValue("IS_NULLABLE", s); // 是否可空
				String scale = tmpdto.getDataValue("NUMERIC_PRECISION", s); // 最大小数位
				String remark = tmpdto.getDataValue("column_COMMENT", s); // 注释
				allfieldnamelist.add(fieldname.toLowerCase().trim());

				nomsign = 0;
				if (fieldname.toLowerCase().equals("pub_jlzt")) {
					pub_jlzt = 1;
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("pub_fjbj")) {
					pub_fjbj = 1;
					nomsign = 1;
				}
				if (nomsign < 1)
					fieldnamelist.add(fieldname.toLowerCase().trim());
				nomsign = 0;
				String[] tmpstr = new String[7];
				tmpstr[0] = fieldname;
				tmpstr[1] = fieldtype;

				if ((ifpk.toUpperCase().trim().equals("PRI"))) {
					tmpstr[2] = "是";
					pkInfo.put(tablename.toLowerCase(), fieldname);
				} else {
					tmpstr[2] = "否";
				}
				tmpstr[3] = maxlength;
				if (isnull.toLowerCase().trim().equals("yes")) {
					tmpstr[4] = "是";
				} else {
					tmpstr[4] = "否";
				}
				tmpstr[5] = scale;
				tmpstr[6] = remark;
				tmphashmap.put(fieldname.toLowerCase().trim(), tmpstr);

			}
		}
/*		if (currentdb.toLowerCase().startsWith("sqlserver")) {

			// sqlserver
			String sqlStr = " select ";
			sqlStr += " a.Name AS column_NAME,";
			sqlStr += " isnull(e.[value],'') as remarks,";
			sqlStr += " b.Name AS data_TYPE,";
			sqlStr += " ColumnProperty(a.object_id,a.Name,'Precision') as length,";
			sqlStr += " ColumnProperty(a.object_id,a.Name,'Precision') as numeric_scale,";
			sqlStr += " isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0) as PRECISIONs, ";
			sqlStr += " ISNULL(IDX.PrimaryKey,'') as column_key,";

			sqlStr += " case when a.is_nullable=1 then '是' else '' end as nulls ";
			sqlStr += " from ";
			sqlStr += " sys.columns a ";
			sqlStr += " left join ";
			sqlStr += " sys.types b on a.user_type_id=b.user_type_id ";
			sqlStr += " inner join ";
			sqlStr += " sys.objects c on a.object_id=c.object_id and c.Type='U' ";
			sqlStr += " left join ";
			sqlStr += " syscomments d on a.default_object_id=d.ID ";
			sqlStr += " left join ";
			sqlStr += " sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 ";
			sqlStr += " left join ";
			sqlStr += " sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ";

			sqlStr += "  LEFT JOIN ";
			sqlStr += " ( ";
			sqlStr += "     SELECT ";
			sqlStr += "         IDXC.[object_id],";
			sqlStr += "         IDXC.column_id,";
			sqlStr += "         Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')";
			sqlStr += "             WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,";
			sqlStr += "         PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'是'ELSE N'' END,";
			sqlStr += "         IndexName=IDX.Name ";
			sqlStr += "     FROM sys.indexes IDX ";
			sqlStr += "     INNER JOIN sys.index_columns IDXC ";
			sqlStr += "         ON IDX.[object_id]=IDXC.[object_id] ";
			sqlStr += "             AND IDX.index_id=IDXC.index_id ";
			sqlStr += "     LEFT JOIN sys.key_constraints KC ";
			sqlStr += "         ON IDX.[object_id]=KC.[parent_object_id] ";
			sqlStr += "             AND IDX.index_id=KC.unique_index_id ";
			sqlStr += "     INNER JOIN  ";
			sqlStr += "     (";
			sqlStr += "         SELECT [object_id], Column_id, index_id=MIN(index_id) ";
			sqlStr += "         FROM sys.index_columns ";
			sqlStr += "         GROUP BY [object_id], Column_id ";
			sqlStr += "     ) IDXCUQ ";
			sqlStr += "         ON IDXC.[object_id]=IDXCUQ.[object_id] ";
			sqlStr += "             AND IDXC.Column_id=IDXCUQ.Column_id";
			sqlStr += "             AND IDXC.index_id=IDXCUQ.index_id";
			sqlStr += " ) IDX";
			sqlStr += "     ON a.[object_id]=IDX.[object_id] ";
			sqlStr += "         AND a.column_id=IDX.column_id ";

			sqlStr += " where c.Name='" + tablename.toUpperCase().trim() + "'";

			DataSimpDtoNew beanms = new DataSimpDtoNew(sqlStr, "AAA");

			nomsign = 0;
			for (int s = 0; s < beanms.getTotRecord(); s++) {
				String fieldname = beanms.getDataValue("column_NAME", s); // 字段名称
				String fieldtype = beanms.getDataValue("data_TYPE", s); // 类型
				String ifpk = beanms.getDataValue("column_key", s); // 是否主键
				String maxlength = beanms.getDataValue("length", s); // 最大长度

				String isnull = beanms.getDataValue("nulls", s); // 是否可空
				String scale = beanms.getDataValue("numeric_scale", s); // 最大小数位
				String remark = beanms.getDataValue("remarks", s); // 注释
				allfieldnamelist.add(fieldname.toLowerCase().trim());
				nomsign = 0;
				nomsign = 0;
				String[] tmpstr = new String[7];
				tmpstr[0] = fieldname;
				tmpstr[1] = fieldtype;

				if ((ifpk.toUpperCase().trim().equals("是"))) {
					tmpstr[2] = "是";
					pkInfo.put(tablename.toLowerCase(), fieldname);
				} else {
					tmpstr[2] = "否";
				}
				tmpstr[3] = maxlength;
				if (isnull.toLowerCase().trim().equals("yes")) {
					tmpstr[4] = "是";
				} else {
					tmpstr[4] = "否";
				}
				tmpstr[5] = scale;
				tmpstr[6] = remark;
				tmphashmap.put(fieldname.toLowerCase().trim(), tmpstr);
			}
		}*/
		fieldInfos.put(tablename.toLowerCase().trim(), fieldnamelist);
		fieldInfo.put(tablename.toLowerCase().trim(), tmphashmap);
		fieldInfosAll.put(tablename.toLowerCase().trim(), allfieldnamelist);
	}

	public static void MakeTableInfo(String currentdb, String tablename) {
		HashMap tmphashmap = new HashMap();

		fieldnamelist = new ArrayList();
		allfieldnamelist = new ArrayList();


		int pub_jlzt = 0;
		int pub_fjbj = 0;
		int pub_zhxgsj = 0;
		int nomsign = 0;


		if (currentdb.toLowerCase().startsWith("mysql")) {

			String sqlStr = " select a.column_NAME,";
			sqlStr += " a.data_TYPE,a.CHARACTER_MAXIMUM_LENGTH,a.NUMERIC_SCALE,a.NUMERIC_PRECISION,a.IS_NULLABLE,a.column_key,a.column_COMMENT from columns a";
			sqlStr += " where " + "trim(a.table_name)='"
					+ tablename.toUpperCase().trim() + "'";

			DataSimpDto tmpdto = new DataSimpDto("mysqltx", sqlStr);

			nomsign = 0;
			for (int s = 0; s < tmpdto.getTotRecord(); s++) {
				String fieldname = tmpdto.getDataValue("column_NAME", s); // 字段名称
				String fieldtype = tmpdto.getDataValue("data_TYPE", s); // 类型
				String ifpk = tmpdto.getDataValue("column_key", s); // 是否主键
				String maxlength = tmpdto.getDataValue(
						"CHARACTER_MAXIMUM_LENGTH", s); // 最大长度

				String isnull = tmpdto.getDataValue("IS_NULLABLE", s); // 是否可空
				String scale = tmpdto.getDataValue("NUMERIC_PRECISION", s); // 最大小数位
				String remark = tmpdto.getDataValue("column_COMMENT", s); // 注释
				allfieldnamelist.add(fieldname.toLowerCase().trim());
				nomsign = 0;
				if (fieldname.toLowerCase().equals("pub_jlzt")) {
					pub_jlzt = 1;
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("pub_fjbj")) {
					pub_fjbj = 1;
					nomsign = 1;
				}
				if (fieldname.toLowerCase().equals("pub_zhxgsj")) {
					pub_zhxgsj = 1;
					nomsign = 1;
				}
				if (nomsign < 1)
					fieldnamelist.add(fieldname.toLowerCase().trim());
				nomsign = 0;
				String[] tmpstr = new String[7];
				tmpstr[0] = fieldname;
				tmpstr[1] = fieldtype;

				if ((ifpk.toUpperCase().trim().equals("PRI"))) {
					tmpstr[2] = "是";
					pkInfo.put(tablename.toLowerCase(), fieldname);
				} else {
					tmpstr[2] = "否";
				}
				tmpstr[3] = maxlength;
				if (isnull.toLowerCase().trim().equals("yes")) {
					tmpstr[4] = "是";
				} else {
					tmpstr[4] = "否";
				}
				tmpstr[5] = scale;
				tmpstr[6] = remark;
				tmphashmap.put(fieldname.toLowerCase().trim(), tmpstr);

			}
		}
/*		if (currentdb.toLowerCase().startsWith("sqlserver")) {

			// sqlserver
			String sqlStr = " select ";
			sqlStr += " a.Name AS column_NAME,";
			sqlStr += " isnull(e.[value],'') as remarks,";
			sqlStr += " b.Name AS data_TYPE,";
			sqlStr += " ColumnProperty(a.object_id,a.Name,'Precision') as length,";
			sqlStr += " ColumnProperty(a.object_id,a.Name,'Precision') as numeric_scale,";
			sqlStr += " isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0) as PRECISIONs, ";
			sqlStr += " ISNULL(IDX.PrimaryKey,'') as column_key,";

			sqlStr += " case when a.is_nullable=1 then '是' else '' end as nulls ";
			sqlStr += " from ";
			sqlStr += " sys.columns a ";
			sqlStr += " left join ";
			sqlStr += " sys.types b on a.user_type_id=b.user_type_id ";
			sqlStr += " inner join ";
			sqlStr += " sys.objects c on a.object_id=c.object_id and c.Type='U' ";
			sqlStr += " left join ";
			sqlStr += " syscomments d on a.default_object_id=d.ID ";
			sqlStr += " left join ";
			sqlStr += " sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 ";
			sqlStr += " left join ";
			sqlStr += " sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ";

			sqlStr += "  LEFT JOIN ";
			sqlStr += " ( ";
			sqlStr += "     SELECT ";
			sqlStr += "         IDXC.[object_id],";
			sqlStr += "         IDXC.column_id,";
			sqlStr += "         Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')";
			sqlStr += "             WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,";
			sqlStr += "         PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'是'ELSE N'' END,";
			sqlStr += "         IndexName=IDX.Name ";
			sqlStr += "     FROM sys.indexes IDX ";
			sqlStr += "     INNER JOIN sys.index_columns IDXC ";
			sqlStr += "         ON IDX.[object_id]=IDXC.[object_id] ";
			sqlStr += "             AND IDX.index_id=IDXC.index_id ";
			sqlStr += "     LEFT JOIN sys.key_constraints KC ";
			sqlStr += "         ON IDX.[object_id]=KC.[parent_object_id] ";
			sqlStr += "             AND IDX.index_id=KC.unique_index_id ";
			sqlStr += "     INNER JOIN  ";
			sqlStr += "     (";
			sqlStr += "         SELECT [object_id], Column_id, index_id=MIN(index_id) ";
			sqlStr += "         FROM sys.index_columns ";
			sqlStr += "         GROUP BY [object_id], Column_id ";
			sqlStr += "     ) IDXCUQ ";
			sqlStr += "         ON IDXC.[object_id]=IDXCUQ.[object_id] ";
			sqlStr += "             AND IDXC.Column_id=IDXCUQ.Column_id";
			sqlStr += "             AND IDXC.index_id=IDXCUQ.index_id";
			sqlStr += " ) IDX";
			sqlStr += "     ON a.[object_id]=IDX.[object_id] ";
			sqlStr += "         AND a.column_id=IDX.column_id ";

			sqlStr += " where c.Name='" + tablename.toUpperCase().trim() + "'";

			DataSimpDtoNew beanms = new DataSimpDtoNew(sqlStr, "AAA");

			nomsign = 0;
			for (int s = 0; s < beanms.getTotRecord(); s++) {
				String fieldname = beanms.getDataValue("column_NAME", s); // 字段名称
				String fieldtype = beanms.getDataValue("data_TYPE", s); // 类型
				String ifpk = beanms.getDataValue("column_key", s); // 是否主键
				String maxlength = beanms.getDataValue("length", s); // 最大长度

				String isnull = beanms.getDataValue("nulls", s); // 是否可空
				String scale = beanms.getDataValue("numeric_scale", s); // 最大小数位
				String remark = beanms.getDataValue("remarks", s); // 注释
				allfieldnamelist.add(fieldname.toLowerCase().trim());
				nomsign = 0;
				nomsign = 0;
				String[] tmpstr = new String[7];
				tmpstr[0] = fieldname;
				tmpstr[1] = fieldtype;

				if ((ifpk.toUpperCase().trim().equals("是"))) {
					tmpstr[2] = "是";
					pkInfo.put(tablename.toLowerCase(), fieldname);
				} else {
					tmpstr[2] = "否";
				}
				tmpstr[3] = maxlength;
				if (isnull.toLowerCase().trim().equals("yes")) {
					tmpstr[4] = "是";
				} else {
					tmpstr[4] = "否";
				}
				tmpstr[5] = scale;
				tmpstr[6] = remark;
				tmphashmap.put(fieldname.toLowerCase().trim(), tmpstr);
			}
		}*/
		fieldInfos.put(tablename.toLowerCase().trim(), fieldnamelist);

/*		if (tablename.toLowerCase().startsWith("t_")) {
			// 加记录状态字段

			if (pub_jlzt < 1) {
				if (currentdb.toLowerCase().startsWith("db2")) {
					String dmlsql = "alter table " + tablename
							+ " add column pub_jlzt char(10)";
					DBOperator.execDML(currentdb, dmlsql);
				} else {
					String dmlsql = "alter table " + tablename
							+ " add pub_jlzt char(10)";
					DBOperator.execDML(currentdb, dmlsql);
				}
			}
		}*/

		fieldInfo.put(tablename.toLowerCase().trim(), tmphashmap);

		fieldInfosAll.put(tablename.toLowerCase().trim(), allfieldnamelist);
	}

	/**
	 * 清空缓存
	 *
	 */
	public static void refresh() {
		fieldInfo = new HashMap();
		pkInfo = new HashMap();

		fieldInfos = new HashMap();
		fieldnamelist = new ArrayList();
		fieldInfosAll = new HashMap();
		allfieldnamelist = new ArrayList();

	}

	/**
	 * 返回表的字段信息不含公共字段
	 *
	 * @param tablename
	 * @return HashMap
	 */
	public ArrayList getAllFieldName(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfos.get(tablename) == null) {
			MakeTableInfo(tablename);
		}
		return (ArrayList) fieldInfos.get(tablename.toLowerCase());
	}

	public ArrayList getAllFieldName(String dbname, String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfos.get(tablename) == null) {
			MakeTableInfo(dbname, tablename);
		}
		return (ArrayList) fieldInfos.get(tablename.toLowerCase());
	}

	/**
	 * 返回表的所有字段信息
	 *
	 *
	 * @param tablename
	 * @return HashMap
	 */
	public ArrayList getWFieldName(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfosAll.get(tablename) == null) {
			MakeTableInfo(tablename);
		}
		return (ArrayList) fieldInfosAll.get(tablename.toLowerCase());
	}

	public ArrayList getWFieldName(String dbname, String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfosAll.get(tablename) == null) {
			MakeTableInfo(dbname, tablename);
		}
		return (ArrayList) fieldInfosAll.get(tablename.toLowerCase());
	}

	/**
	 * 建立表的相关信息
	 *
	 * @param tablename
	 * @return HashMap
	 */
	public void toTableInfo(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename.toLowerCase()) == null) {
			MakeTableInfo(tablename.toLowerCase());
		}
	}

	/**
	 * 返回表的相关信息
	 *
	 * @param tablename
	 * @return HashMap
	 */
	public HashMap getTableInfo(String dbname, String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeTableInfo(dbname, tablename);
		}
		return (HashMap) fieldInfo.get(tablename.toLowerCase());
	}

	public HashMap getTableInfo(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeTableInfo(tablename);
		}
		return (HashMap) fieldInfo.get(tablename.toLowerCase());
	}

	/**
	 * 返回表的某一字段的类型
	 *
	 *
	 * @param tablename
	 * @param fieldname
	 * @return String 字符/数字/日期/日期时间
	 */
	public String getFiledInfo(String tablename, String fieldname) {
		tablename = tablename.toLowerCase();

		if (fieldInfo.get(tablename.toLowerCase()) == null) {
			MakeTableInfo(tablename.toLowerCase());
		}
		HashMap tableinfo = (HashMap) fieldInfo.get(tablename.toLowerCase());
		String[] fieldinfo = (String[]) tableinfo.get(fieldname.toLowerCase());
		String typevalue = (String) fieldinfo[1];
		typevalue = typevalue.toLowerCase();

		if (typevalue.equals("varchar")) {
			typevalue = "字符";
		}
		if (typevalue.equals("varchar2")) {
			typevalue = "字符";
		}
		if (typevalue.equals("char")) {
			typevalue = "字符";
		}
		if (typevalue.startsWith("timestmp")) {
			typevalue = "日期时间";
		}
		if (typevalue.startsWith("timestamp")) {
			typevalue = "日期时间";
		}
		if (typevalue.equals("blob")) {
			typevalue = "字符";
		}
		if (typevalue.equals("text")) {
			typevalue = "字符";
		}
		if (typevalue.equals("smallint")) {
			typevalue = "数字";
		}
		if (typevalue.equals("tinyint")) {
			typevalue = "数字";
		}
		if (typevalue.equals("bigint")) {
			typevalue = "数字";
		}
		if (typevalue.equals("double")) {
			typevalue = "数字";
		}
		if (typevalue.equals("float")) {
			typevalue = "数字";
		}
		if (typevalue.equals("decimal")) {
			typevalue = "数字";
		}
		if (typevalue.equals("number")) {
			typevalue = "数字";
		}
		if (typevalue.equals("dec")) {
			typevalue = "数字";
		}
		if (typevalue.equals("real")) {
			typevalue = "数字";
		}
		if (typevalue.equals("clob")) {
			typevalue = "字符";
		}
		if (typevalue.equals("long varchar")) {
			typevalue = "字符";
		}
		if (typevalue.equals("date")) {
			typevalue = "日期";
		}
		return typevalue;
	}

	/**
	 * 返回表的主键名称
	 *
	 * @param String
	 *            tablename
	 * @return String
	 */
	public String getTablePk(String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeTableInfo(tablename);
		}

		return (String) pkInfo.get(tablename.toLowerCase());
	}

	public String getTablePk(String dbname, String tablename) {
		tablename = tablename.toLowerCase();
		if (fieldInfo.get(tablename) == null) {
			MakeTableInfo(dbname, tablename);
		}

		return (String) pkInfo.get(tablename.toLowerCase());
	}

	/**
	 * 句柄，供调用
	 */
	public synchronized static DBTableInfo getInst() {
		if (instance == null) {
			instance = new DBTableInfo();
		}
		return instance;
	}

}